﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Bule.Malbilden.Domain;
using Bule.Malbilden.Data;
using NHibernate.Tool.hbm2ddl;
using NHibernate.Cfg;
using NHibernate;
using System.Data;
using System.Globalization;

namespace DatabaseCreator
{
    public class DatabaseCreator
    {
        private const string NAME_COLUMN = "Livsmedel";
        private const string KCAL_COLUMN = "Energi kcal (kcal)";
        private const string PROTEIN_COLUMN = "Protein (g)";
        private const string CARBS_COLUMN = "Kolhydrat (g)";
        private const string SATURATED_FAT_COLUMN = "Mättade fettsyror (g)";
        private const string MONOUNSATURATED_FAT_COLUMN = "Enkelomättade fettsyror (g)";
        private const string POLYUNSATURATED_FAT_COLUMN = "Fleromättade fettsyror (g)";
        private const string FIBER_COLUMN = "Fibrer (g)";
        private const string VITAMIN_A_COLUMN = "Retinolekvivalent (ug)";
        private const string VITAMIN_B1_COLUMN = "Tiamin (mg)";
        private const string VITAMIN_B2_COLUMN = "Riboflavin (mg)";
        private const string VITAMIN_B3_COLUMN = "Niacin (mg)";
        private const string VITAMIN_B6_COLUMN = "Vitamin B6 (mg)";
        private const string VITAMIN_B12_COLUMN = "Vitamin B12 (ug)";
        private const string VITAMIN_C_COLUMN = "Askorbinsyra (mg)";
        private const string VITAMIN_D_COLUMN = "D vitamin (ug)";
        private const string VITAMIN_E_COLUMN = "a-Tokoferol (mg)";
        private const string FOLIC_ACID_COLUMN = "Folat (ug)";
        private const string CALCIUM_COLUMN = "Kalcium (mg)";
        private const string IRON_COLUMN = "Järn (mg)";
        private const string SODIUM_COLUMN = "Natrium (mg)";
        private const string POTASSIUM_COLUMN = "Kalium (mg)";
        private const string PHOSPHORUS_COLUMN = "Fosfor (mg)";
        private const string MAGNESIUM_COLUMN = "Magnesium (mg)";
        private const string ZINC_COLUMN = "Zink (mg)";
        private const string SELENIUM_COLUMN = "Selen (ug)";

        private ISessionFactory sessionFactory;

        public void AddTestData()
        {
            using (var session = sessionFactory.OpenSession())
            {
                NutritionData n1 = new NutritionData(20, 20, 20, 20, 20, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12);
                NutritionData n2 = new NutritionData(100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100);

                var nutritionDataRepository = new Repository<NutritionData>();
                nutritionDataRepository.Add(n1);
                nutritionDataRepository.Add(n2);

                FoodProduct fp1 = new FoodProduct("Ris", "dl", 70, n1);
                FoodProduct fp2 = new FoodProduct("Saft", "glas", 250, n2);
                var foodProductRepository = new Repository<FoodProduct>();
                foodProductRepository.Add(fp1);
                foodProductRepository.Add(fp2);

                EatenItem ef1 = new EatenItem(fp1, 100, new DateTime(2008, 01, 01));
                EatenItem ef2 = new EatenItem(fp1, 100);
                EatenItem ef3 = new EatenItem(fp2, 200);
                var eatenItemRepository = new Repository<EatenItem>();
                eatenItemRepository.Add(ef1);
                eatenItemRepository.Add(ef2);
                eatenItemRepository.Add(ef3);

                Meal m1 = new Meal("Saftris", new List<IEaten>() { ef2, ef3 }, "hög", 1.3);
                var mealRepository = new Repository<Meal>();
                mealRepository.Add(m1);

                EatenItem em1 = new EatenItem(m1, 2, new DateTime(2008, 01, 01));
                eatenItemRepository.Add(em1);

                DayNutritionGoal g1 = new DayNutritionGoal(2800, 150, 350, 50, 20, new DateTime(2007, 12, 13));
                DayNutritionGoal g2 = new DayNutritionGoal(2000, 100, 300, 100, 30, new DateTime(2008, 01, 01));
                var goalRepository = new Repository<DayNutritionGoal>();
                goalRepository.Add(g1);
                goalRepository.Add(g2);

                Person p1 = new Person("Kajsa Hällgren", 30, true, true, false);
                p1.AddNutritionGoal(g1);
                p1.AddNutritionGoal(g2);
                p1.AddEatenItem(ef1);
                p1.AddEatenItem(em1);

                Person p2 = new Person("Palle Kuling", 20, false);

                var personRepository = new Repository<Person>();
                personRepository.Add(p1);
                personRepository.Add(p2);
            }
        }

        public void AddDataFromExcelSheet(string filename)
        {
            var foodProductRepository = new Repository<FoodProduct>();
            var nutritionDataRepository = new Repository<NutritionData>();
            
            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(
                "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + filename + ";" +
                "Extended Properties=Excel 8.0;");

            System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter("select * from [Livsmedel$]", connection);

            DataTable table = new DataTable();
            adapter.Fill(table);

            foreach (DataRow excelRow in table.Rows)
            {
                try
                {
                    // Set values for columns in excel database:
                    NutritionData nutritionalValue = new NutritionData(
                        Convert.ToDouble(excelRow[KCAL_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[PROTEIN_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[CARBS_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[SATURATED_FAT_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[MONOUNSATURATED_FAT_COLUMN]) +
                            Convert.ToDouble(excelRow[POLYUNSATURATED_FAT_COLUMN]),
                        Convert.ToDouble(excelRow[FIBER_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[VITAMIN_A_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[VITAMIN_B1_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[VITAMIN_B2_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[VITAMIN_B3_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[VITAMIN_B6_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[VITAMIN_B12_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[VITAMIN_C_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[VITAMIN_D_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[VITAMIN_E_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[FOLIC_ACID_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[CALCIUM_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[IRON_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[SODIUM_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[POTASSIUM_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[PHOSPHORUS_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[MAGNESIUM_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[ZINC_COLUMN], CultureInfo.InvariantCulture),
                        Convert.ToDouble(excelRow[SELENIUM_COLUMN], CultureInfo.InvariantCulture));

                    FoodProduct product = new FoodProduct((string)excelRow[NAME_COLUMN], nutritionalValue);

                    nutritionDataRepository.Add(nutritionalValue);
                    foodProductRepository.Add(product);
                }
                catch (Exception e)
                {
                    Console.WriteLine(excelRow[NAME_COLUMN]);
                }
            }
        }

        public void CreateSchema()
        {
            var configuration = new Configuration();
            configuration.Configure();
            configuration.AddAssembly(typeof(Person).Assembly);
            sessionFactory = configuration.BuildSessionFactory();
            new SchemaExport(configuration).Execute(true, true, false, false);
        }
    }
}
